<?php


namespace app\controller;

use app\BaseController;
use think\Request;
use think\Response;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use app\common\Snowflake;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Search extends BaseController
{

    // 统一响应格式
    private function jsonResponse(int $code, string $msg, $data = []): Response
    {
        return json(compact('code', 'msg', 'data'));
    }

    /**
     * 记录搜索关键词到搜索表
     */
    public function create()
    {
        $keyword = $this->request->param('keyword');
        $user_id = $this->request->param('user_id');

        if (!$keyword || !$user_id) {
            return json([
                'code' => 1,
                'msg' => '关键词和用户ID不能为空',
                'data' => []
            ]);
        }

        $record = Db::name('search_records')->where('keyword', $keyword)->find();

        if ($record) {
            // 如果关键词已存在，更新搜索次数和搜索用户数
            Db::name('search_records')->where('id', $record['id'])
                ->inc('search_count')
                ->when(!in_array($user_id, explode(',', $record['user_id'])), function ($query) use ($user_id, $record) {
                    $new_user_ids = $record['user_id'] ? $record['user_id'] . ',' . $user_id : $user_id;
                    $query->setField('user_id', $new_user_ids)
                        ->inc('user_count');
                })
                ->update();
        } else {
            // 如果关键词不存在，插入新记录
            Db::name('search_records')->insert([
                'keyword' => $keyword,
                'search_count' => 1,
                'user_count' => 1,
                'user_id' => $user_id
            ]);
        }

        return json([
            'code' => 0,
            'msg' => '搜索记录成功',
            'data' => []
        ]);
    }

    /**
     * 根据筛选条件获取搜索记录列表
     */
    public function list()
    {
        $keyword = $this->request->param('keyword');
        $min_count = $this->request->param('min_count', 0);
        $max_count = $this->request->param('max_count', PHP_INT_MAX);
        $min_user_count = $this->request->param('min_user_count', 0);
        $max_user_count = $this->request->param('max_user_count', PHP_INT_MAX);
        $start_time = $this->request->param('start_time');
        $end_time = $this->request->param('end_time');
        $time_range = $this->request->param('time_range');
        $page = $this->request->param('page', 1); // 获取当前页码，默认第1页
        $pageSize = $this->request->param('pageSize', 10); // 获取每页数量，默认10条

        $query = Db::name('search_records');

        if ($keyword) {
            $query->where('keyword', 'like', "%{$keyword}%");
        }

        $query->where('search_count', '>=', $min_count)
            ->where('search_count', '<=', $max_count)
            ->where('user_count', '>=', $min_user_count)
            ->where('user_count', '<=', $max_user_count);

        switch ($time_range) {
            case 'today':
                $start = date('Y-m-d 00:00:00');
                $end = date('Y-m-d 23:59:59');
                $query->whereBetweenTime('search_time', $start, $end);
                break;
            case 'yesterday':
                $start = date('Y-m-d 00:00:00', strtotime('-1 day'));
                $end = date('Y-m-d 23:59:59', strtotime('-1 day'));
                $query->whereBetweenTime('search_time', $start, $end);
                break;
            case 'last_7_days':
                $start = date('Y-m-d 00:00:00', strtotime('-7 days'));
                $end = date('Y-m-d 23:59:59');
                $query->whereBetweenTime('search_time', $start, $end);
                break;
            case 'last_30_days':
                $start = date('Y-m-d 00:00:00', strtotime('-30 days'));
                $end = date('Y-m-d 23:59:59');
                $query->whereBetweenTime('search_time', $start, $end);
                break;
            default:
                if ($start_time && $end_time) {
                    $query->whereBetweenTime('search_time', $start_time, $end_time);
                }
        }

        $list = $query->field('id, keyword, search_count, user_count')
            ->paginate([
                'list_rows' => $pageSize,
                'page' => $page
            ]);

        return json([
            'code' => 0,
            'msg' => 'OK',
            'data' => [
                'total' => $list->total(),
                'per_page' => $list->listRows(),
                'current_page' => $list->currentPage(),
                'last_page' => $list->lastPage(),
                'data' => $list->items()
            ]
        ]);
    }

    /**
     * 导出搜索记录到 CSV 文件
     */
    public function export()
    {
        $keyword = $this->request->param('keyword');
        $min_count = $this->request->param('min_count', 0);
        $max_count = $this->request->param('max_count', PHP_INT_MAX);
        $min_user_count = $this->request->param('min_user_count', 0);
        $max_user_count = $this->request->param('max_user_count', PHP_INT_MAX);
        $start_time = $this->request->param('start_time');
        $end_time = $this->request->param('end_time');
        $time_range = $this->request->param('time_range');

        $query = Db::name('search_records');

        if ($keyword) {
            $query->where('keyword', 'like', "%{$keyword}%");
        }

        $query->where('search_count', '>=', $min_count)
            ->where('search_count', '<=', $max_count)
            ->where('user_count', '>=', $min_user_count)
            ->where('user_count', '<=', $max_user_count);

        switch ($time_range) {
            case 'today':
                $start = date('Y-m-d 00:00:00');
                $end = date('Y-m-d 23:59:59');
                $query->whereBetweenTime('search_time', $start, $end);
                break;
            case 'yesterday':
                $start = date('Y-m-d 00:00:00', strtotime('-1 day'));
                $end = date('Y-m-d 23:59:59', strtotime('-1 day'));
                $query->whereBetweenTime('search_time', $start, $end);
                break;
            case 'last_7_days':
                $start = date('Y-m-d 00:00:00', strtotime('-7 days'));
                $end = date('Y-m-d 23:59:59');
                $query->whereBetweenTime('search_time', $start, $end);
                break;
            case 'last_30_days':
                $start = date('Y-m-d 00:00:00', strtotime('-30 days'));
                $end = date('Y-m-d 23:59:59');
                $query->whereBetweenTime('search_time', $start, $end);
                break;
            default:
                if ($start_time && $end_time) {
                    $query->whereBetweenTime('search_time', $start_time, $end_time);
                }
        }

        $list = $query->field('id, keyword, search_count, user_count')->select();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置表头
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', '搜索关键词');
        $sheet->setCellValue('C1', '搜索次数');
        $sheet->setCellValue('D1', '搜索用户数');

        // 填充数据
        $row = 2;
        foreach ($list as $item) {
            $sheet->setCellValue('A' . $row, $item['id']);
            $sheet->setCellValue('B' . $row, $item['keyword']);
            $sheet->setCellValue('C' . $row, $item['search_count']);
            $sheet->setCellValue('D' . $row, $item['user_count']);
            $row++;
        }

        // 输出 Excel 文件
        $filename = date('YmdHis') . '_search.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
    }

}